/*
 Navicat Premium Data Transfer

 Source Server         : localSQL
 Source Server Type    : SQL Server
 Source Server Version : 14001000
 Source Host           : localhost:1433
 Source Catalog        : hotel2
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 14001000
 File Encoding         : 65001

 Date: 02/06/2020 23:58:51
*/


-- ----------------------------
-- Table structure for billOrder
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[billOrder]') AND type IN ('U'))
	DROP TABLE [dbo].[billOrder]
GO

CREATE TABLE [dbo].[billOrder] (
  [orderId] int IDENTITY(1,1) NOT NULL,
  [prepay] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [total] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [num] varchar(20) COLLATE Chinese_PRC_CI_AS NULL
)
GO

ALTER TABLE [dbo].[billOrder] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [billOrder]
-- ----------------------------
SET IDENTITY_INSERT [dbo].[billOrder] ON
GO

INSERT INTO [dbo].[billOrder] ([orderId], [prepay], [total], [num]) VALUES (N'1', N'10500', N'10000', N'245606')
GO

SET IDENTITY_INSERT [dbo].[billOrder] OFF
GO


-- ----------------------------
-- Table structure for guest
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[guest]') AND type IN ('U'))
	DROP TABLE [dbo].[guest]
GO

CREATE TABLE [dbo].[guest] (
  [personId] int IDENTITY(1,1) NOT NULL,
  [guestId] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [name] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [tel] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [age] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [sex] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [day] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [stayDay] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL
)
GO

ALTER TABLE [dbo].[guest] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [guest]
-- ----------------------------
SET IDENTITY_INSERT [dbo].[guest] ON
GO

INSERT INTO [dbo].[guest] ([personId], [guestId], [name], [tel], [age], [sex], [day], [stayDay]) VALUES (N'1', N'2456064131', N'杨千旺', N'18306687609', NULL, N'男', N'2020-06-01', N'7')
GO

INSERT INTO [dbo].[guest] ([personId], [guestId], [name], [tel], [age], [sex], [day], [stayDay]) VALUES (N'2', N'2456064132', N'邹龙生', N'18306657604', NULL, N'男', N'2020-06-01', N'7')
GO

SET IDENTITY_INSERT [dbo].[guest] OFF
GO


-- ----------------------------
-- Table structure for lives
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[lives]') AND type IN ('U'))
	DROP TABLE [dbo].[lives]
GO

CREATE TABLE [dbo].[lives] (
  [orderId] int NOT NULL,
  [personId] int NOT NULL,
  [roomNum] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [status] varchar(1) COLLATE Chinese_PRC_CI_AS NOT NULL
)
GO

ALTER TABLE [dbo].[lives] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [lives]
-- ----------------------------
INSERT INTO [dbo].[lives]  VALUES (N'1', N'1', N'101', N'0')
GO

INSERT INTO [dbo].[lives]  VALUES (N'1', N'2', N'101', N'0')
GO


-- ----------------------------
-- Table structure for manager
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[manager]') AND type IN ('U'))
	DROP TABLE [dbo].[manager]
GO

CREATE TABLE [dbo].[manager] (
  [num] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [pw] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [name] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL
)
GO

ALTER TABLE [dbo].[manager] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [manager]
-- ----------------------------
INSERT INTO [dbo].[manager]  VALUES (N'245606', N'1111', N'loser')
GO


-- ----------------------------
-- Table structure for rate
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[rate]') AND type IN ('U'))
	DROP TABLE [dbo].[rate]
GO

CREATE TABLE [dbo].[rate] (
  [money] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [prepay] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL
)
GO

ALTER TABLE [dbo].[rate] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [rate]
-- ----------------------------
INSERT INTO [dbo].[rate]  VALUES (N'100', N'150')
GO

INSERT INTO [dbo].[rate]  VALUES (N'1000', N'1500')
GO

INSERT INTO [dbo].[rate]  VALUES (N'150', N'200')
GO

INSERT INTO [dbo].[rate]  VALUES (N'200', N'250')
GO


-- ----------------------------
-- Table structure for room
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[room]') AND type IN ('U'))
	DROP TABLE [dbo].[room]
GO

CREATE TABLE [dbo].[room] (
  [roomNum] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
  [area] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [roomtype] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [money] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
  [status] varchar(20) COLLATE Chinese_PRC_CI_AS NULL
)
GO

ALTER TABLE [dbo].[room] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [room]
-- ----------------------------
INSERT INTO [dbo].[room]  VALUES (N'101', N'250', N'豪华套房', N'1000', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'102', N'205', N'豪华套房', N'1000', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'103', N'250', N'豪华套房', N'1000', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'104', N'250', N'豪华套房', N'1000', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'105', N'250', N'豪华套房', N'1000', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'106', N'250', N'豪华套房', N'1000', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'201', N'100', N'单人房', N'100', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'202', N'100', N'单人房', N'100', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'203', N'100', N'单人房', N'100', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'204', N'100', N'单人房', N'100', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'205', N'100', N'单人房', N'100', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'206', N'100', N'单人房', N'100', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'301', N'150', N'标准房', N'150', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'302', N'150', N'标准房', N'150', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'303', N'150', N'标准房', N'150', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'304', N'150', N'标准房', N'150', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'305', N'150', N'标准房', N'150', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'306', N'150', N'标准房', N'150', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'401', N'200', N'高级标间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'402', N'200', N'高级标间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'403', N'200', N'高级标间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'404', N'200', N'高级标间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'405', N'200', N'高级标间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'406', N'200', N'高级标间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'501', N'200', N'三人间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'502', N'200', N'三人间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'503', N'200', N'三人间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'504', N'200', N'三人间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'505', N'200', N'三人间', N'200', N'0')
GO

INSERT INTO [dbo].[room]  VALUES (N'506', N'200', N'三人间', N'200', N'0')
GO


-- ----------------------------
-- Table structure for type
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[type]') AND type IN ('U'))
	DROP TABLE [dbo].[type]
GO

CREATE TABLE [dbo].[type] (
  [roomType] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL
)
GO

ALTER TABLE [dbo].[type] SET (LOCK_ESCALATION = TABLE)
GO


-- ----------------------------
-- Records of [type]
-- ----------------------------
INSERT INTO [dbo].[type]  VALUES (N'标准房')
GO

INSERT INTO [dbo].[type]  VALUES (N'单人房')
GO

INSERT INTO [dbo].[type]  VALUES (N'高级标间')
GO

INSERT INTO [dbo].[type]  VALUES (N'豪华套房')
GO

INSERT INTO [dbo].[type]  VALUES (N'三人间')
GO


-- ----------------------------
-- Primary Key structure for table billOrder
-- ----------------------------
ALTER TABLE [dbo].[billOrder] ADD CONSTRAINT [PK__billOrde__0809335DD0ED6C5D] PRIMARY KEY CLUSTERED ([orderId])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table guest
-- ----------------------------
ALTER TABLE [dbo].[guest] ADD CONSTRAINT [PK__guest__EC7D7D4D2B4CF817] PRIMARY KEY CLUSTERED ([personId])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table lives
-- ----------------------------
ALTER TABLE [dbo].[lives] ADD CONSTRAINT [PK__lives__EB205D6A492B364D] PRIMARY KEY CLUSTERED ([orderId], [personId], [roomNum])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table manager
-- ----------------------------
ALTER TABLE [dbo].[manager] ADD CONSTRAINT [PK__manager__DF908D657F22312C] PRIMARY KEY CLUSTERED ([num])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table rate
-- ----------------------------
ALTER TABLE [dbo].[rate] ADD CONSTRAINT [PK__rate__0DDBEE9F565176DB] PRIMARY KEY CLUSTERED ([money])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table room
-- ----------------------------
ALTER TABLE [dbo].[room] ADD CONSTRAINT [PK__room__EEB9E32C0FC828AA] PRIMARY KEY CLUSTERED ([roomNum])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table type
-- ----------------------------
ALTER TABLE [dbo].[type] ADD CONSTRAINT [PK__type__B631E7B3C86209AE] PRIMARY KEY CLUSTERED ([roomType])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Foreign Keys structure for table billOrder
-- ----------------------------
ALTER TABLE [dbo].[billOrder] ADD CONSTRAINT [FK__billOrder__num__693CA210] FOREIGN KEY ([num]) REFERENCES [manager] ([num]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


-- ----------------------------
-- Foreign Keys structure for table lives
-- ----------------------------
ALTER TABLE [dbo].[lives] ADD CONSTRAINT [FK__lives__orderId__74AE54BC] FOREIGN KEY ([orderId]) REFERENCES [billOrder] ([orderId]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[lives] ADD CONSTRAINT [FK__lives__personId__75A278F5] FOREIGN KEY ([personId]) REFERENCES [guest] ([personId]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[lives] ADD CONSTRAINT [FK__lives__roomNum__76969D2E] FOREIGN KEY ([roomNum]) REFERENCES [room] ([roomNum]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


-- ----------------------------
-- Foreign Keys structure for table room
-- ----------------------------
ALTER TABLE [dbo].[room] ADD CONSTRAINT [FK__room__roomtype__70DDC3D8] FOREIGN KEY ([roomtype]) REFERENCES [type] ([roomType]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

ALTER TABLE [dbo].[room] ADD CONSTRAINT [FK__room__money__71D1E811] FOREIGN KEY ([money]) REFERENCES [rate] ([money]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

